// 数据库状态检查脚本
const mysql = require('mysql2/promise');
const config = require('./config');

async function checkDatabase() {
    let connection;
    
    try {
        console.log('🔍 检查数据库状态...\n');
        
        // 连接数据库
        connection = await mysql.createConnection(config.mysql);
        console.log('✅ 数据库连接成功\n');
        
        // 1. 检查表是否存在
        console.log('📋 1. 检查表结构...');
        const [tables] = await connection.execute(`
            SHOW TABLES LIKE '${config.table.name}'
        `);
        
        if (tables.length === 0) {
            console.log('❌ 表不存在，请先运行 init-db.sql');
            return;
        }
        console.log('✅ 表存在\n');
        
        // 2. 检查表结构
        console.log('📋 2. 检查表结构...');
        const [columns] = await connection.execute(`
            DESCRIBE ${config.table.name}
        `);
        console.log('表字段:');
        columns.forEach(col => {
            console.log(`  ${col.Field}: ${col.Type} ${col.Null === 'NO' ? 'NOT NULL' : 'NULL'} ${col.Key || ''}`);
        });
        console.log('');
        
        // 3. 检查唯一约束
        console.log('📋 3. 检查唯一约束...');
        const [indexes] = await connection.execute(`
            SHOW INDEX FROM ${config.table.name} WHERE Key_name = 'uk_order_number'
        `);
        
        if (indexes.length > 0) {
            console.log('✅ 订单编号唯一约束已存在');
        } else {
            console.log('❌ 订单编号唯一约束不存在');
        }
        console.log('');
        
        // 4. 检查数据量
        console.log('📋 4. 检查数据量...');
        const [countResult] = await connection.execute(`
            SELECT COUNT(*) as total FROM ${config.table.name}
        `);
        console.log(`总记录数: ${countResult[0].total}`);
        
        // 5. 检查重复数据
        console.log('\n📋 5. 检查重复数据...');
        const [duplicates] = await connection.execute(`
            SELECT 
                order_number, 
                COUNT(*) as count
            FROM ${config.table.name} 
            GROUP BY order_number 
            HAVING COUNT(*) > 1
        `);
        
        if (duplicates.length === 0) {
            console.log('✅ 没有重复的订单编号');
        } else {
            console.log(`❌ 发现 ${duplicates.length} 个重复的订单编号:`);
            duplicates.forEach(dup => {
                console.log(`  ${dup.order_number}: ${dup.count} 条记录`);
            });
            console.log('\n💡 建议运行 fix-database.sql 修复重复数据');
        }
        
        // 6. 显示示例数据
        console.log('\n📋 6. 显示示例数据...');
        const [sampleData] = await connection.execute(`
            SELECT order_number, price, upload_time 
            FROM ${config.table.name} 
            ORDER BY upload_time DESC 
            LIMIT 5
        `);
        
        if (sampleData.length > 0) {
            console.log('最近5条记录:');
            sampleData.forEach((row, index) => {
                console.log(`  ${index + 1}. 订单: ${row.order_number}, 金额: ${row.price}, 时间: ${row.upload_time}`);
            });
        } else {
            console.log('暂无数据');
        }
        
    } catch (error) {
        console.error('❌ 检查失败:', error.message);
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// 运行检查
if (require.main === module) {
    checkDatabase();
}

module.exports = { checkDatabase }; 